/**
 * 
 */
package mwa.cs.dao;

import java.util.List;
import java.util.Map;

import mwa.cs.database.Database;
import mwa.cs.model.CustHH;
import mwa.cs.util.JDate;

/**
 * A class used for managing cust_hh
 */
public class CustHHDAO {

	/** a database of this cust_hh */
    private Database db;
    
    /**
     * Instantiate a new object of this class
     * @param database a database
     */
    public CustHHDAO(Database db) {
        this.db = db;
    }
    
    /**
     * a method for searching all cust_hh in database
     * @return all cust_hh
     */
    public List<Map<String, Object>> findAll() {
        String vaSql = "SELECT * FROM cust_hh";
        List<Map<String, Object>> result = db.queryList(vaSql);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    /**
     * Preparing data in a new month (meter system)
     * @param paPeriodMonth last month in format: 'MM'
     * @param paPeriodYear last year in format: 'YYYY'
     */
    public void InsertCustHHPrepareData(int pnSiteId, String paPeriodMonth, String paPeriodYear, 
    		int pnUserId, String paDate, String paTime){
    	String vaSql = "INSERT INTO cust_hh (site_id, zone_id, route_id, account, period_month, period_year, seq, pname, fname, lname, "
    			+ "customer_class, allowance, tax_id, address, contact_address, bill_number, meter_number, meter_size, total_bill, "
    			+ "total_amount, previous_read_date, previous_read_number, average_consumption, average_consumption_new, " 
    			+ "new_consumption, current_read_date, current_read_time, current_read_number, comment_code, "
    			+ "status, water_charge, water_fee, vat_amount, bill_amount, download_date, download_time, upload_date, upload_time, "
    			+ "account_receivable_date, account_receivable_time, print_bill_number, print_serial_number, " 
    			+ "wastewater_charge, garbage_charge, other_charge, number_of_day, "
    			+ "insert_user_id,insert_date, insert_time, update_user_id, update_date, update_time) "
    			+ "SELECT cust.site_id, cust.zone_id, cust.route_id, cust.account, " 
    			+ "(SELECT CASE "
    			+ "WHEN (CAST(period_month AS INTEGER) + 1) > 12 THEN '01' "
    			+ "ELSE \"TRIM\"(\"TO_CHAR\"((CAST(period_month AS INTEGER) + 1), '00')) "
    			+ "END "
    			+ "FROM cust_monthly " 
    			+ "WHERE account = cust.account and site_id = cust.site_id and period_month = ? and period_year = ?), " 
    			+ "(SELECT	CASE "
    			+ "WHEN (CAST(period_month AS INTEGER) +1) > 12 THEN \"TRIM\"(\"TO_CHAR\"((CAST(period_year AS INTEGER) + 1), '0000')) "
    			+ "ELSE period_year "
    			+ "END "
    			+ "FROM cust_monthly " 
    			+ "WHERE  account = cust.account and site_id = cust.site_id and period_month = ? and period_year = ?), " 
    			//+ "cust.seq, "
    			+ "(SELECT seq FROM route_detail WHERE account = cust.account and site_id = cust.site_id), "
    			+ "(SELECT pname FROM customer WHERE account = cust.account and site_id = cust.site_id), " 
    			+ "(SELECT fname FROM customer WHERE account = cust.account and site_id = cust.site_id), " 
    			+ "(SELECT lname FROM customer WHERE account = cust.account and site_id = cust.site_id), " 
    			+ "cust.customer_class, cust.allowance, " 
    			+ "(SELECT tax_id FROM customer WHERE account = cust.account and site_id = cust.site_id), " 
    			+ "(SELECT home_number || ' ' || address || ' ต.' || "
    			+ "(SELECT name FROM address WHERE province = province_code and district = district_code and city = city_code) || '| อ.' || "
    			+ "(SELECT name FROM address WHERE province = province_code and city = '00' and district = district_code) || ' จ.' || "
    			+ "(SELECT name FROM address WHERE city = '00' and district = '00' and province = province_code) || ' ' || "
    			+ "post_code FROM customer WHERE account = cust.account and site_id = cust.site_id), " 
    			+ "(SELECT contact_home_number || ' ' || contact_address || ' ต.' ||"
    			+ "(SELECT name FROM address WHERE province = contact_province_code and district = contact_district_code and city = contact_city_code) || '| อ.' || "
    			+ "(SELECT name FROM address WHERE province = contact_province_code and city = '00' and district = contact_district_code) || ' จ.' || "
    			+ "(SELECT name FROM address WHERE city = '00' and district = '00' and province = contact_province_code) || ' ' || "
    			+ "contact_post_code FROM customer WHERE account = cust.account and site_id = cust.site_id), " 
    			+ "'0000000000', " 
    			+ "cust.meter_number, " 
    			+ "(SELECT meter_size FROM customer WHERE account = cust.account and site_id = cust.site_id), " 
    			+ "(SELECT count(*) FROM ar WHERE account = cust.account and site_id = cust.site_id and status IS null and receive_date IS null), " 
    			+ "(SELECT SUM(total_amount) FROM ar WHERE account = cust.account and site_id = cust.site_id and status IS null and receive_date IS null), "  
    			+ "cust.read_date, cust.meter_read_number, cust.average_consumption, 0, 0, null, null, 0, null, " 
    			+ "'1', 0, 0, 0, 0, null, null, null, null, null, null, null, null, " 
    			+ "(SELECT wastewater_charge FROM customer WHERE account = cust.account and site_id = cust.site_id), " 
    			+ "(SELECT garbage_charge FROM customer WHERE account = cust.account and site_id = cust.site_id), " 
    			+ "(SELECT other_charge FROM customer WHERE account = cust.account and site_id = cust.site_id), "  
    			+ "0, ?, ?, ?, 0, null, null "
    			+ "FROM cust_monthly cust WHERE cust.site_id = ? and cust.period_month = ? and cust.period_year = ? and cust.status = 2 and cust.zone_id <> 0";
        try {
            db.insert(vaSql, paPeriodMonth, paPeriodYear, paPeriodMonth, paPeriodYear,
            		pnUserId, paDate, paTime, pnSiteId, paPeriodMonth, paPeriodYear);
        } catch (Exception e) {
        	throw new RuntimeException(e);
        }
    }
    
    public List<Map<String, Object>> findCustHH(int pnSiteId, String paPeriodMonth, String paPeriodYear) {
    	String vaSql = "SELECT site_id, account, period_month, period_year, bill_number "
    			+ "FROM cust_hh WHERE site_id = ? and period_month = ? and period_year = ?";
        List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId, paPeriodMonth, paPeriodYear);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    public void updateBillNumberInCustHH(int pnSiteId, String paAccount, String paBillNumber, 
    		String paPeriodMonth, String paPeriodYear, int pnUserId, String paDate, String paTime) {
    	String vaSql = "UPDATE cust_hh SET bill_number = ?, update_user_id = ?, update_date = ?, update_time = ? "
        		+ "WHERE site_id = ? and account = ? and period_month = ? and period_year = ?";
        try {
        	db.update(vaSql,
        			paBillNumber,
        			pnUserId,
        			paDate,
        			paTime,
                	pnSiteId,
                	paAccount,
                	paPeriodMonth,
                	paPeriodYear);
        } catch (Exception e) {
        	throw new RuntimeException(e);
        }
    }
    
    /**
     * finding max month by site_id
     * @param pnSiteId site_id that will find max month
     * @return max month
     */
    public String findMaxMonth(int pnSiteId) {
    	String vaSql = "SELECT MAX(period_month) AS period_month FROM cust_hh "
    			+ "WHERE period_year = (SELECT MAX(period_year) FROM cust_hh) and site_id = ?";
    	Map<String, Object> result = db.querySingle(vaSql, pnSiteId);
        if (result.get("PERIOD_MONTH") != null) {
            return result.get("PERIOD_MONTH").toString();
        } else {
            return "00";
        }
    }
    
    /**
     * finding max year by site_id
     * @param pnSiteId site_id that will find max year
     * @return max year
     */
    public String findMaxYear(int pnSiteId) {
    	String vaSql = "SELECT MAX(period_year) AS period_year FROM cust_hh WHERE site_id = ?";
    	Map<String, Object> result = db.querySingle(vaSql, pnSiteId);
        if (result.get("PERIOD_YEAR") != null) {
            return result.get("PERIOD_YEAR").toString();
        } else {
            return "0000";
        }
    }
    
    /**
     * count cust_hh in this period_month and period_year
     * @param pnSiteId site that will count
     * @param paPeriodMonth period month that will count
     * @param paPeriodYear period_year that will count
     * @return counting number of cust_hh
     */
    public int CountCustHHInPeriod(int pnSiteId, String paPeriodMonth, String paPeriodYear) {
        String vaSql = "SELECT count(*) FROM cust_hh WHERE site_id = ? and period_month = ? and period_year = ?";
        Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paPeriodMonth, paPeriodYear);
        if (result != null) {
            return Integer.parseInt(result.get("COUNT(*)").toString());
        } else {
            return 0;
        }
    }
    
    /**
     * finding insert_user_id, insert_date, insert_time in the recent period_month and period_year
     * @param pnSiteId site that will find user
     * @param paPeriodMonth period_month that will find user
     * @param paPeriodYear period_year that will find user
     * @return String array of insert_user_id, insert_date, insert_time
     */
    public String[] findInsertUser(int pnSiteId, String paPeriodMonth, String paPeriodYear) {
    	String vaSql = "SELECT DISTINCT insert_user_id, insert_date, insert_time FROM cust_hh WHERE site_id = ? "
    			+ "and period_month = ? and period_year = ?";
    	Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paPeriodMonth, paPeriodYear);
        if (result != null) {
        	String[] vaResult = new String[3];
        	vaResult[0] = result.get("INSERT_USER_ID").toString();
        	vaResult[1] = result.get("INSERT_DATE").toString();
        	vaResult[2] = result.get("INSERT_TIME").toString();
            return vaResult;
        } else {
            return null;
        }
    }
    
    public void insertCustHHByAccount(CustHH poCustHH) {
    	String vaSql = "INSERT INTO cust_hh" + " (site_id, zone_id, route_id, account, period_month, period_year, seq, "
        		+ "pname, fname, lname, customer_class, allowance, tax_id, address, contact_address, bill_number, meter_number, meter_size, total_bill, "
    			+ "total_amount, previous_read_date, previous_read_time, previous_number, average_consumption, average_consumption_new, new_consumption, "
        		+ "current_read_date, current_read_time, current_read_number, comment_code,status, water_charge, water_fee, vat_amount, "
    			+ "bill_amount, download_date, download_time, upload_date, upload_time, account_receivable_date, account_receivable_time, print_bill_number, "
        		+ "print_serial_number, wastewater_charge, garbage_charge, other_charge, number_of_day, insert_user_id, insert_date, insert_time, update_user_id, "
    			+ "update_date, update_time)" 
        		+ " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try {
            db.insert(vaSql,poCustHH.getVnSiteId(),
            		poCustHH.getVnZoneId(),
            		poCustHH.getVnRouteId(),
            		poCustHH.getVaAccount(),
            		poCustHH.getVaPeriodMonth(),
            		poCustHH.getVaPeriodYear(),
            		poCustHH.getVnSeq(),
            		poCustHH.getVaPname(),
            		poCustHH.getVaFname(),
            		poCustHH.getVaLname(),
            		poCustHH.getVaCustomerClass(),
            		poCustHH.getVaAllowance(),
            		poCustHH.getVaTaxId(),
            		poCustHH.getVaAddress(),
            		poCustHH.getVaContactAddress(),
            		poCustHH.getVaBillNumber(),
            		poCustHH.getVaMeterNumber(),
            		poCustHH.getVaMeterSize(),
            		poCustHH.getVnTotalBill(),
            		poCustHH.getVnTotalAmount(),
            		poCustHH.getVaPreviousReadDate(),
            		poCustHH.getVaPreviousReadTime(),
            		poCustHH.getVnPreviousReadNumber(),
            		poCustHH.getVnAveragevnConsumption(),
            		poCustHH.getVnAveragevnConsumptionNew(),
            		poCustHH.getVnNewConsumption(),
            		poCustHH.getVaCurrentReadDate(),
            		poCustHH.getVaCurrentReadTime(),
            		poCustHH.getVnCurrentReadNumber(),
            		poCustHH.getVaCommentCode(),
            		poCustHH.getVaStatus(),
            		poCustHH.getVnWaterCharge(),
            		poCustHH.getVnWaterFee(),
            		poCustHH.getVnVatAmount(),
            		poCustHH.getVnBillAmount(),
            		poCustHH.getVaDownloadDate(),
            		poCustHH.getVaDownloadTime(),
            		poCustHH.getVaUploadDate(),
            		poCustHH.getVaUploadTime(),
            		poCustHH.getVaAccountReceivableDate(),
            		poCustHH.getVaAccountReceivableTime(),
            		poCustHH.getVaPrintSerailNumber(),
            		poCustHH.getPrintBillNumber(),
            		poCustHH.getVnWasteWater_charge(),
            		poCustHH.getVnGrabageCharge(),
            		poCustHH.getVnOtherCharge(),
            		poCustHH.getVnNumberfDay(),
            		poCustHH.getVnInsertUserId(),
            		poCustHH.getVaInsertDate(),
            		poCustHH.getVaInsertTime(),
            		poCustHH.getVnUpdateUserId(),
            		poCustHH.getVaUpdateDate(),
            		poCustHH.getVaUpdateTime());
        } catch (Exception e) {
        	throw new RuntimeException(e);
        }
    }
    
    public List<Map<String, Object>> findCustHHByRoute(int pnSiteId,String paZoneId, String paRouteId, String paAccount, String paPeriodMonth, String paPeriodYear, int pnStart, int pnEnd) {
    	if(paZoneId.equals("")){
    		paZoneId ="%";
    	}
    	if(paRouteId.equals("")){
    		paRouteId ="%";
    	}
    	if(paAccount.equals("")){
    		paAccount ="%";
    	}
    	String vaSql = "SELECT * FROM (SELECT ROWNUM as rnum,seq,zone_name,route_description,account,previous_read_date,previous_read_number,period_month,period_year FROM (SELECT * "
    			+ "FROM cust_hh,zone,route WHERE cust_hh.zone_id = zone.zone_id AND cust_hh.route_id = route.route_id  AND zone.zone_id = route.zone_id AND cust_hh.site_id = ?"
    			+ "AND cust_hh.period_month = ? AND cust_hh.period_year = ? AND (COALESCE(cust_hh.zone_id, -1) LIKE '"+paZoneId+"') AND (COALESCE(cust_hh.route_id, -1) LIKE '"+paRouteId+"') "
    			+ "AND (COALESCE(cust_hh.account, ' ') LIKE '"+paAccount+"') AND (cust_hh.status IS NULL OR cust_hh.status != '2') ORDER BY cust_hh.seq asc) WHERE ROWNUM <=?) WHERE rnum >?";
        List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId, paPeriodMonth, paPeriodYear, pnEnd, pnStart);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    public int getCountCustHHByRoute(int pnSiteId,String paZoneId, String paRouteId, String paAccount, String paPeriodMonth, String paPeriodYear, int pnStart, int pnEnd) {
    	if(paZoneId.equals("")){
    		paZoneId ="%";
    	}
    	if(paRouteId.equals("")){
    		paRouteId ="%";
    	}
    	if(paAccount.equals("")){
    		paAccount ="%";
    	}
    	String vaSql = "SELECT COUNT(*) FROM (SELECT ROWNUM as rnum,seq,zone_name,route_description,account,previous_read_date,previous_read_number,period_month,period_year FROM (SELECT * "
    			+ "FROM cust_hh,zone,route WHERE cust_hh.zone_id = zone.zone_id AND cust_hh.route_id = route.route_id  AND zone.zone_id = route.zone_id AND cust_hh.site_id = ?"
    			+ "AND cust_hh.period_month = ? AND cust_hh.period_year = ? AND (COALESCE(cust_hh.zone_id, -1) LIKE '"+paZoneId+"') AND (COALESCE(cust_hh.route_id, -1) LIKE '"+paRouteId+"') "
    			+ "AND (COALESCE(cust_hh.account, ' ') LIKE '"+paAccount+"') AND (cust_hh.status IS NULL OR cust_hh.status != '2') ORDER BY cust_hh.seq asc) WHERE ROWNUM <=?) WHERE rnum >?";
        Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paPeriodMonth, paPeriodYear, pnEnd, pnStart);
        if (result != null) {
        	return Integer.parseInt(result.get("COUNT(*)").toString());
        } else {
            return 0;
        }
    }
    
    public List<Map<String, Object>> findAllCustHHByRoute(int pnSiteId,String paZoneId, String paRouteId, String paAccount, String paPeriodMonth, String paPeriodYear) {
    	if(paZoneId.equals("")){
    		paZoneId ="%";
    	}
    	if(paRouteId.equals("")){
    		paRouteId ="%";
    	}
    	if(paAccount.equals("")){
    		paAccount ="%";
    	}
    	String vaSql = "SELECT ROWNUM as rnum,cust_hh.seq,zone.zone_name,route.route_description,cust_hh.account,cust_hh.previous_read_date,cust_hh.previous_read_number,period_month,period_year "
    			+ "FROM cust_hh,zone,route WHERE cust_hh.zone_id = zone.zone_id AND cust_hh.route_id = route.route_id  AND zone.zone_id = route.zone_id AND cust_hh.site_id = ?"
    			+ "AND cust_hh.period_month = ? AND cust_hh.period_year = ? AND (COALESCE(cust_hh.zone_id, -1) LIKE '"+paZoneId+"') AND (COALESCE(cust_hh.route_id, -1) LIKE '"+paRouteId+"') "
    			+ "AND (COALESCE(cust_hh.account, ' ') LIKE '"+paAccount+"') AND (cust_hh.status IS NULL OR cust_hh.status != '2') ORDER BY cust_hh.seq asc";
        List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId, paPeriodMonth, paPeriodYear);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    public int getCountAllCustHHByRoute(int pnSiteId,String paZoneId, String paRouteId, String paAccount, String paPeriodMonth, String paPeriodYear) {
    	if(paZoneId.equals("")){
    		paZoneId ="%";
    	}
    	if(paRouteId.equals("")){
    		paRouteId ="%";
    	}
    	if(paAccount.equals("")){
    		paAccount ="%";
    	}
    	String vaSql = "SELECT COUNT(*) FROM (SELECT ROWNUM as rnum,cust_hh.seq,zone.zone_name,route.route_description,cust_hh.account,cust_hh.previous_read_date,cust_hh.previous_read_number,period_month,period_year "
    			+ "FROM cust_hh,zone,route WHERE cust_hh.zone_id = zone.zone_id AND cust_hh.route_id = route.route_id  AND zone.zone_id = route.zone_id AND cust_hh.site_id = ?"
    			+ "AND cust_hh.period_month = ? AND cust_hh.period_year = ? AND (COALESCE(cust_hh.zone_id, -1) LIKE '"+paZoneId+"') AND (COALESCE(cust_hh.route_id, -1) LIKE '"+paRouteId+"') "
    			+ "AND (COALESCE(cust_hh.account, ' ') LIKE '"+paAccount+"') AND (cust_hh.status IS NULL OR cust_hh.status != '2') ORDER BY cust_hh.seq asc)";
        Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paPeriodMonth, paPeriodYear);
        if (result != null) {
        	return Integer.parseInt(result.get("COUNT(*)").toString());
        } else {
            return 0;
        }
    }
    
    public Map<String, Object> findCustHHByAccount(int pnSiteId,String paAccount, 
    		String paPeriodMonth, String paPeriodYear) {
    	String vaSql = "SELECT cust_hh.*,meter_size.fee "
    			+ "FROM cust_hh,meter_size WHERE cust_hh.meter_size = meter_size.meter_size_code "
    			+ "AND cust_hh.site_id = ? AND cust_hh.account = ? AND cust_hh.period_month = ? AND cust_hh.period_year = ?";
        Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paAccount, paPeriodMonth, paPeriodYear);
        if (result != null) {
        	return result;
        } else {
            return null;
        }
    }
    

    public void updateCustHHByRoute(CustHH poCustHH) {
    	String vaSql = "UPDATE cust_hh SET average_consumption_new = ? , new_consumption = ? , current_read_date = ? , "
    			+ "current_read_number = ? , status = ? , water_charge = ? , water_fee = ? , "
    			+ "vat_amount = ? , bill_amount = ? , upload_date = ? , upload_time = ? , account_receivable_date = ? , "
    			+ "account_receivable_time = ? , number_of_day = ? , update_user_id = ?, update_date = ? , update_time = ? "
    			+ "WHERE site_id = ? AND zone_id = ? AND route_id = ? AND account = ? AND period_month = ? AND period_year = ? ";
    	
        try {
        	db.update(vaSql,
        			poCustHH.getVnAveragevnConsumptionNew(),
        			poCustHH.getVnNewConsumption(),
        			poCustHH.getVaCurrentReadDate(),
        			poCustHH.getVnCurrentReadNumber(),
        			poCustHH.getVaStatus(),
        			poCustHH.getVnWaterCharge(),
        			poCustHH.getVnWaterFee(),
        			poCustHH.getVnVatAmount(),
        			poCustHH.getVnBillAmount(),
        			poCustHH.getVaUploadDate(),
        			poCustHH.getVaUploadTime(),
        			poCustHH.getVaAccountReceivableDate(),
        			poCustHH.getVaAccountReceivableTime(),
        			poCustHH.getVnNumberfDay(),
        			poCustHH.getVnUpdateUserId(),
        			poCustHH.getVaUpdateDate(),
        			poCustHH.getVaUpdateTime(),
        			poCustHH.getVnSiteId(),
        			poCustHH.getVnZoneId(),
        			poCustHH.getVnRouteId(),
        			poCustHH.getVaAccount(),
        			poCustHH.getVaPeriodMonth(),
        			poCustHH.getVaPeriodYear()
        			
        			);
        } catch (Exception e) {
        	e.printStackTrace();
        	throw new RuntimeException(e);
        }
    }
    
    //แก้ไข AR การอ่านมาตร
    public boolean updateCustHH(CustHH poCustHH) {
    	String vaSql = "UPDATE cust_hh SET previous_read_date = ? , previous_read_number = ? , average_consumption_new = ? , "
    			+ "new_consumption = ? , current_read_date = ? , current_read_number = ? , comment_code = ? , "
    			+ "water_charge = ? , water_fee = ? , vat_amount = ? , bill_amount = ? , upload_date = ? , upload_time = ? , "
    			+ "wastewater_charge = ? , garbage_charge = ? , other_charge = ? , number_of_day = ? , update_user_id = ? , "
    			+ "update_date = ? , update_time = ? WHERE site_id = ? AND account = ? AND period_month = ? AND period_year = ? ";
    	
    	boolean result = false;
        try {
        	db.update(vaSql,poCustHH.getVaPreviousReadDate(),
        			poCustHH.getVnPreviousReadNumber(),
        			poCustHH.getVnAveragevnConsumptionNew(),
        			poCustHH.getVnNewConsumption(),
        			poCustHH.getVaCurrentReadDate(),
        			poCustHH.getVnCurrentReadNumber(),
        			poCustHH.getVaCommentCode(),
        			poCustHH.getVnWaterCharge(),
        			poCustHH.getVnWaterFee(),
        			poCustHH.getVnVatAmount(),
        			poCustHH.getVnBillAmount(),
        			poCustHH.getVaUploadDate(),
        			poCustHH.getVaUploadTime(),
        			poCustHH.getVnWasteWater_charge(),
        			poCustHH.getVnGrabageCharge(),
        			poCustHH.getVnOtherCharge(),
        			poCustHH.getVnNumberfDay(),
        			poCustHH.getVnUpdateUserId(),
        			poCustHH.getVaUpdateDate(),
        			poCustHH.getVaUpdateTime(),
        			poCustHH.getVnSiteId(),
        			poCustHH.getVaAccount(),
        			poCustHH.getVaPeriodMonth(),
        			poCustHH.getVaPeriodYear()
        			);
        	result = true;
        } catch (Exception e) {
        	System.out.println(e);
        	result = false;
        }
        return result;
    }
    
 // ค้นหาข้อมูล อ่านค่ามาตร และ วันที่ในการอ่านค่า สำหรับหน้า บันทึกใบแจ้งหนี้ CS42001Srvl
    public Map<String, Object> getMeterAndDate(int pnSiteId, String paAccount, String paPeriodMount, String paPeriodYear) {
    	String vaSql = "SELECT substr(previous_read_date,7,2) || '/' || substr(previous_read_date,5,2) || '/' || substr(previous_read_date,1,4) as previous_read_date, "
    			+ " substr(current_read_date,7,2) || '/' || substr(current_read_date,5,2) || '/' || substr(current_read_date,1,4) as current_read_date, "
    			+ " previous_read_number, current_read_number "
    			+ " from cust_hh "
    			+ " where site_id = ? and account = ? and period_month = ? "
				+ " and period_year = ? ";
        Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paAccount, paPeriodMount, paPeriodYear);
        if (result != null) {
        	return result;
        } else {
            return null;
        }
    }
 
    public boolean deletePrepareData(String paCurrentMonth, String paCurentYear, int pnSiteId) {
        String vaSql = "DELETE FROM cust_hh WHERE period_month = ? AND period_year = ? AND site_id = ?";
        try {
            db.update(vaSql, paCurrentMonth, paCurentYear, pnSiteId);
            return true;
        } catch (Exception e) {
            return false;
        }
    }

	public void updateDownloadDate(int pnSiteId,int pnZoneId , int pnRouteId, String paPeriodMonth, String paPeriodYear) {
    	String vaSql = "UPDATE cust_hh SET download_date = ?,download_time = ? WHERE site_id = ? AND zone_id = ? AND route_id = ? AND period_month = ? AND period_year = ? ";
        try {
        	db.update(vaSql, JDate.getDate(), JDate.getTime(), pnSiteId, pnZoneId, pnRouteId, paPeriodMonth, paPeriodYear);
		} catch (Exception e) {
			e.printStackTrace();
		}	
	}
    
	public int countAccountAmount(int pnSiteId,int pnZoneId ,int pnRoute, String paPeriodMonth, String paPeriodYear) {
    	String vaSql = "SELECT count(account) AS account_amount FROM cust_hh "
    			+ "WHERE site_id = ? AND zone_id = ? AND route_id = ? AND period_month = ? AND period_year = ? AND download_date IS NOT NULL ";
    	Map<String, Object> result = db.querySingle(vaSql, pnSiteId, pnZoneId, pnRoute, paPeriodMonth, paPeriodYear);
    	int vaDowoadAmount = 0;
    	try {
    		vaDowoadAmount = Integer.parseInt( result.get("ACCOUNT_AMOUNT").toString());
		} catch (Exception e) {
			e.printStackTrace();
		} 	
    	return vaDowoadAmount;
	}
}